package com.xjt.excel.util;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;


public class FileUtils {
	private final static String excel2003L = ".xls"; // 2003- 版本的excel
	private final static String excel2007U = ".xlsx"; // 2007+ 版本的excel
	
	
	/**
	 * 从数据列表中读取数据,并将数据读入列表中
	 * 
	 * @return
	 */
	


	/**
	 * 描述：根据文件后缀，自适应上传文件的版本
	 * 
	 * @param inStr
	 *            ,fileName
	 * @return
	 * @throws Exception
	 */
	public Workbook getWorkbook(InputStream inStr, String fileName)
			throws Exception {
		Workbook wb = null;
		String fileType = fileName.substring(fileName.lastIndexOf("."));
		System.out.println(fileType);

		if (excel2003L.equals(fileType)) {
			System.out.println("2003");
			wb = new HSSFWorkbook(inStr); // 2003-
		} else if (excel2007U.equals(fileType) || ".xlsm".equals(fileType)) {
			System.out.println("2007");
			wb = new XSSFWorkbook(inStr); // 2007+
		} else {
			throw new Exception("解析的文件格式有误！");
		}
		return wb;
	}

	public Map JXEXCEL(InputStream inModel, String fileNamemModel,
			String tableName, String QDICTTYPE, int mergeLine) throws Exception {
		System.out.println(mergeLine);
		System.out.println("临时表名为"+tableName);
		String pcId = getUUID();
		System.out.println(pcId);
		// List<String> listTop = new ArrayList<String>();
		List<HashMap> listTop = new ArrayList<HashMap>();
		List<HashMap> listUnder = new ArrayList<HashMap>();
		Map map = new HashMap<String, String>();
		Workbook workModel = this.getWorkbook(inModel, fileNamemModel); // 创建模板工作空间
		if (null == workModel) {
			throw new Exception("创建Excel工作薄为空！");
		}
		Sheet sheet = null;
		sheet = workModel.getSheetAt(0); // 读取第一个页签
		Map<String, String> mapUnder; // 里面每一条数据组装成一个map
		Map<String, String> topMap;
		Map<String, String> MergedRegionsMap;
	
		int count = 1;
		int topCount = 1;
		String resultSql;
		
		int lastRowLine=0;
		int firstRowLine=0;
		int rowNum=0;

		int rangeNum=0;
		

		MergedRegionsMap=getMergedRegionValue(sheet);
		

		   int sheetMergeCount = sheet.getNumMergedRegions();   
		   
		  if(sheetMergeCount>0){
			  lastRowLine=15365782; //标识
	       for (int i =0; i < sheetMergeCount; i++) {   
	             CellRangeAddress range = sheet.getMergedRegion(i);   
	            /* int firstColumn = range.getFirstColumn(); 
	             int lastColumn = range.getLastColumn();   
	             int firstRow = range.getFirstRow();   */
	             int lastRow = range.getLastRow();  
	             int firstRow = range.getFirstRow();
	             if(rangeNum==0&&firstRow<=mergeLine){
	            lastRowLine=lastRow;
	            firstRowLine=firstRow;
	            rangeNum++;
	             }
	             if(lastRow>lastRowLine&&firstRow<=mergeLine){
	            	 lastRowLine=lastRow;
	            	 firstRowLine=firstRow;
	            	 
	             }
	             
	                 } 
		  }
	       
	       
	       
		System.out.println("lastRowLine="+lastRowLine);
		System.out.println("firstRow="+firstRowLine);
		

		
	
		
		if(sheetMergeCount>0&&lastRowLine!=15365782){ //说明有合并单元格 并且 是起始行为5以下的格子
			
			rowNum=lastRowLine+1;
		}
	
		
		
		
		System.out.println("一共"+sheet.getLastRowNum());
		Row row=null;
		int topAllNum = 0; // 表头大小
	//	for (Row row : sheet) {
			for (; rowNum <= sheet.getLastRowNum(); rowNum++) {
				 row = sheet.getRow(rowNum);
				 
				 
				 
					// 如果当前行没有数据，跳出循环
				 if(row==null){
					 
					 continue; 
				 }
				 
					if (!judgeRowIsNull(row, row.getLastCellNum())) {
						System.out.println(rowNum+"行为空");
						continue;
					}
			
	
			if (count == 1) { // 读取页签
            System.out.println("表头在"+rowNum);
				for (Cell cell3 : row) {

					if (getCellValue(cell3).toString().length() != 0) {
						topMap = new HashMap<String, String>();

						topMap.put("topIndex", "A" + topCount);
						topMap.put("topTip", (String) getCellValue(cell3));
						// topMap.put("topTip","1");
						topCount++;
						listTop.add((HashMap) topMap);
					}
				}
				topAllNum = listTop.size(); // 记录表头的列数
				count++;
				continue;
			}
			
			
			if (!judgeRowIsNull(row,topAllNum)) {
				System.out.println(rowNum+"行为空");
				continue;
			}

		
           System.out.println("第"+rowNum);
			
			
			//MergedRegionsMap
			int count11 = 1;
			String excelValue=""; 
			String keyName=""; //rowNum
			mapUnder = new HashMap<String, String>();

			resultSql = "insert into " + tableName + "  SELECT "; // 先做占位符
			for (int i = 1; i <= topAllNum; i++) {
				
				if(!MergedRegionsMap.isEmpty()){
					keyName=rowNum+"-"+(i-1);
					
					if(MergedRegionsMap.containsKey(keyName)){
						excelValue=MergedRegionsMap.get(keyName);
						MergedRegionsMap.remove(keyName);
					}
					else{
						excelValue=(String) getCellValue(row.getCell(i - 1));
					}
				}else{
				
					excelValue=(String) getCellValue(row.getCell(i - 1));
				}
				
				

				if (count11 != topAllNum) {
					resultSql = resultSql + "'"
							+ excelValue + "',";
				} else {

					resultSql = resultSql + "'"
							+ excelValue
							+ "' from dual";
				}

				mapUnder.put("QSQL", resultSql);
				mapUnder.put("QDICTTYPE", QDICTTYPE); // 类别ID
				mapUnder.put("QPCID", pcId);
				count11++;

			}

			listUnder.add((HashMap) mapUnder);
		}

		System.out.println("表头长度=" + listTop.size());
		inModel.close();
		map.put("topFlag", listTop);
		map.put("Under", listUnder);
		map.put("num", listTop.size());
		map.put("pcId", pcId);

		return map;
	}

	/**
	 * 描述：对表格中数值进行格式化
	 * 
	 * @param cell
	 * @return
	 */
	public static Object getCellValue(Cell cell) {
		Object value = null;
		DecimalFormat df = new DecimalFormat("0"); // 格式化number String字符
		SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); // 日期格式化
		DecimalFormat df2 = new DecimalFormat("0.00"); // 格式化数字
		if (cell != null) {
			switch (cell.getCellType()) {
				case STRING:
				value = cell.getStringCellValue().toString()
						.replaceAll("'", "").replace("\"", "").trim();
				

				break;

				case _NONE:
					break;
				case NUMERIC:

				if (HSSFDateUtil.isCellDateFormatted(cell)
						||cell.getCellStyle().getDataFormat()==28||cell.getCellStyle().getDataFormat()==31) {
					System.out.println("是日期类型");
					value = sdf.format(cell.getDateCellValue());

				} 
				
				
				
				else {
					try{
					value = df.format(cell.getNumericCellValue())+"";
				}catch(Exception e){
					
					value="";
				}
				
				}
				
				break;

				case BOOLEAN:
				value = cell.getBooleanCellValue();

				break;

				case FORMULA:
					break;
				case BLANK:
				value = "";

				break;

				case ERROR:
					break;
				default:  //只剩下表达式类型和未知类型
			//	value = "";
				
				
				try{
				value=cell.getStringCellValue().toString().replaceAll("'", "").replace("\"", "").trim();
				
				
				}
				catch(Exception e){
				
					try{
					value=df.format(cell.getNumericCellValue());
					}catch(Exception ee){
						value=""; //未知类型数据
						
					}
				}
				System.out.println("进来了3" );
				break;
			}

			return value;
		} else {

			return "";

		}
	}

	public static String diG(String str) {
		if (str.endsWith("0")) {
			str = str.substring(0, str.length() - 2);
			System.out.println("截取后" + str);
			return diG(str);
		} else {
			return str;
		}

	}

	// 生成32位的随机数
	public static String getUUID() {
		UUID uuid = UUID.randomUUID();

		return uuid.toString().replace("-", "");

	}

	public boolean judgeRowIsNull(Row row, int num) {

	
		boolean flag = false;
		if(row!=null){
		for (int i = 0; i <= num - 1; i++) {

			if (getCellValue(row.getCell(i)).toString().trim().length() != 0) {
				flag = true;
			
				//getCellValue(row.getCell(i))
				
				break;

			}

		}
		}

		return flag;

	}
	
	
	
	
	
	
	
	/**
     * 获取合并单元格的值
     * 
     * @param sheet
     * @return
     */
    public static Map<String, String> getMergedRegionValue(Sheet sheet) {
    	System.out.println("进来了");
        int sheetMergeCount = sheet.getNumMergedRegions();
        String tmp="";
        String key="";
        Map<String, String> map = new HashMap<String, String>(); //用于存放合并值
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
            System.out.println(firstRow+" "+lastRow+" ");
            System.out.println(firstColumn+" "+lastColumn+" ");
      
 
         
            Row fRow = sheet.getRow(firstRow);
            
            if(fRow!=null){
            
            Cell fCell = fRow.getCell(firstColumn);
           
            tmp=(String) getCellValue(fCell);
          
            
            
            for(int ii=firstRow;ii<=lastRow;ii++){
            	
            	for(int j=firstColumn;j<=lastColumn;j++){
                	
            		key=ii+"-"+j;
            		
            		map.put(key,tmp);

                }
            	
            	
            	
            	
            }
        }
            
        }
        return map;
    }

	
	
	
	
	
	

}